from sqlalchemy import Column, Integer, String, Float, DateTime, Text, ForeignKey, Boolean, Index
from sqlalchemy.orm import relationship
from backend.database.database import Base
import datetime

class ItemSKU(Base):
    __tablename__ = "item_sku"
    id = Column(Integer, primary_key=True, index=True)
    item_name = Column(Text, nullable=False, comment='商品名称', index=True)
    cleaned_name = Column(Text, comment='清洗后的名称', index=True)
    item_brand = Column(Text, comment='品牌', index=True)
    item_specs = Column(Text, comment='规格')
    item_price = Column(Float, comment='价格')
    base_price = Column(Float, comment='基础价格')
    base_unit = Column(String(32), comment='基础单位')
    base_num = Column(Float, comment='基础数量')
    package_type = Column(String(64), comment='包装类型')
    item_classification = Column(String(128), comment='分类', index=True)
    item_img = Column(String(255), comment='商品图片')
    sku = Column(String(64), unique=True, index=True, comment='SKU编码')
    is_handled = Column(Boolean, default=False, comment='是否已处理')
    human_tag = Column(Boolean, comment='是否人工标注')
    created_at = Column(DateTime, default=datetime.datetime.now, index=True)
    updated_at = Column(DateTime, onupdate=datetime.datetime.now)

    # 添加复合索引
    __table_args__ = (
        Index('idx_sku_search', 'item_name', 'item_brand', 'item_classification'),
    ) 

class Item(Base):
    __tablename__ = 'item'
    # id	int
    id = Column(Integer, primary_key=True, index=True)
    # item_name	text
    item_name = Column(Text, comment='商品名称', index=True)
    # item_brand	text
    item_brand = Column(Text, comment='品牌', index=True)
    # item_specs	text
    item_specs = Column(Text, comment='规格')
    # cleaned_name	text
    cleaned_name = Column(Text, comment='清洗后的名称', index=True)
    # item_price	double
    item_price = Column(Float, comment='价格')
    # item_classification	text
    item_classification = Column(String(128), comment='分类', index=True)
    # base_price	double
    base_price = Column(Float, comment='基础价格')
    # base_num	float
    base_num = Column(Float, comment='基础数量')
    # base_unit	text
    base_unit = Column(String(32), comment='基础单位')
    # package_type	text
    package_type = Column(String(64), comment='包装类型')
    # shop_id	int
    shop_id = Column(Integer, comment='店铺ID', index=True)
    # shop_name	text
    shop_name = Column(Text, comment='店铺名称', index=True)
    # shop_store_id	int
    shop_store_id = Column(Integer, comment='店铺ID', index=True)
    # sku_id	text
    sku_id = Column(String(100), comment='SKU编码', index=True)
    # item_img	text
    item_img = Column(String(500), comment='商品图片')
    # run_id	text
    run_id = Column(String(100), comment='运行ID', index=True)
    # created_at	datetime
    created_at = Column(DateTime, default=datetime.datetime.now, index=True)
    # updated_at	datetime
    updated_at = Column(DateTime, onupdate=datetime.datetime.now)
